﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Reflection;

namespace test
{
    /// <summary>
    /// SQL查询语言构建器
    /// 
    /// </summary>
    class SqlBuilder<T>
    {
        protected string sqlString;
        T model;
        protected bool isNotAll;
        string tmp;
        char[] tmpc;
        //string output;
        //int findCount;

        public SqlBuilder()
        {
            model = Activator.CreateInstance<T>();
            isNotAll = true;
        }

        public string GetPropertyName(T model, object value)
        {
            foreach (PropertyInfo info in model.GetType().GetProperties())
            {
                if (info.GetValue(model, null).ToString() == value.ToString())
                {
                    return info.Name;
                }
            }
            return string.Empty;
            //throw new Exception("Not Found This Property");
        }

        //public string GetPropertyNames(T model, object[] value)
        //{
        //    output = "";
        //    findCount = 0;
        //    foreach (PropertyInfo info in model.GetType().GetProperties())
        //    {
        //        if (info.GetValue(model, null).ToString() == value[findCount].ToString())
        //        {
        //            output += info.Name + ",";
        //            findCount++;
        //        }
        //    }
        //    return output;
        //    //throw new Exception("Not Found This Property");
        //}

        public void clear()
        {
            this.sqlString = "";
        }

        public SqlBuilder<T> Select()
        {
            isNotAll = false;
            this.sqlString += "select ";
            return this;
        }

        public SqlBuilder<T> Select(params object[] area)
        {
            isNotAll = true;
            this.sqlString += "select ";
            tmp = "";

            //if (typeof(string).Equals(typeof(T)) )
            //{
                for (int iter = 0; iter < area.Length; iter++)
                {
                    tmp += area[0].GetType().ReflectedType.Name + "." + area[iter] + ",";
                }                
            //}
            //else
            //{
            //    for (int iter = 0; iter < area.Length; iter++)
            //    {
            //        tmp += GetPropertyName(model, area[iter]) + ",";	
            //    }                
            //}           
            
           
            this.sqlString += tmp.Substring(0, tmp.Length - 1);
            return this;
        }


        /// <summary>
        /// 默认使用此方法最佳
        /// </summary>
        /// <returns></returns>
        public SqlBuilder<T> From()
        {
            if (this.isNotAll)
                this.sqlString += " from " + model.GetType().Name;
            else
                this.sqlString += " * from " + model.GetType().Name;
            return this;
        }

        /// <summary>
        /// 对于普通字符串或类似可转换为的支持，
        /// 不赞同使用，对于实体对象名称变更
        /// 会导致运行是查询不到
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public SqlBuilder<T> From(object tableName)
        {
            if (this.isNotAll)
                this.sqlString += " from " + tableName.ToString();
            else
                this.sqlString += " * from " + tableName.ToString();
            return this;
        }

        /// <summary>
        /// 对于普通字符串或类似可转换为的支持，
        /// 不赞同使用，对于实体对象名称变更
        /// 会导致运行是查询不到
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public SqlBuilder<T> From(params object[] tableName)
        {
            tmp = ""; //清空tmp变量

            for (int iter = 0; iter < tableName.Length; iter++)
            {
                tmp += tableName[iter] + ",";
            }
            tmp = tmp.Substring(0, tmp.Length - 1);
            //
            if (this.isNotAll)
                this.sqlString += " from " + tmp;
            else
                this.sqlString += " * from " + tmp;
            //
            return this;
        }

        public SqlBuilder<T> Into()
        {
            if (this.isNotAll)
                this.sqlString += " INTO ";
            else
                this.sqlString += " * INTO ";
            return this;
        }

        public SqlBuilder<T> Distinct(object field)
        {
            this.sqlString = " distinct "
                + field.GetType().ReflectedType.Name 
                + "."
                + field.ToString();
            //this.sqlString.Replace("*", " distinct " + field);
            return this;
        }

        public SqlBuilder<T> Top(int number)
        {
            this.sqlString += " top " + number;
            return this;
        }

        public SqlBuilder<T> PERCENT()
        {
            this.sqlString += " PERCENT ";
            return this;
        }


        public SqlBuilder<T> Where(object field)
        {
            this.sqlString += " where " 
                + field.GetType().ReflectedType.Name 
                + "."
                + field.ToString();
            return this;
        }


        /// <summary>
        /// like 通配符
        ///  %	替代一个或多个字符
        ///  _	仅替代一个字符
        /// [charlist]	字符列中的任何单一字符
        /// [^charlist]
        /// 或者
        /// [!charlist]
        /// </summary>
        /// <param name="val"></param>
        /// <returns></returns>
        public SqlBuilder<T> Like(object val)
        {
            this.sqlString += " like " + val.ToString();
            return this;
        }


        public SqlBuilder<T> In(object[] vals)
        {
            tmp = " in (";
            for (int iter = 0; iter < vals.Length; iter++)
            {
                tmp += vals[iter].ToString() + ",";
            }
            this.sqlString += tmp.Substring(0, tmp.Length - 1) + ")";
            return this;
        }


        public SqlBuilder<T> Union()
        {
            this.sqlString += " UNION ";
            return this;
        }

        public SqlBuilder<T> UnionAll()
        {
            this.sqlString += " UNION ALL ";
            return this;
        }

        public SqlBuilder<T> As(string val)
        {
            this.sqlString += " as " + val;
            return this;
        }

        public SqlBuilder<T> Between(object valBegin, object valEnd)
        {
            this.sqlString +=
                " between  '" + valBegin.ToString() + "' and '" + valEnd.ToString() + "'";
            return this;
        }


        public SqlBuilder<T> IsNull()
        {
            this.sqlString += " IS NULL ";
            return this;
        }

        public SqlBuilder<T> IsNotNull()
        {
            this.sqlString += " IS NOT NULL ";
            return this;
        }

        public SqlBuilder<T> Equals(object val)
        {
            if(val is int)
            {
                this.sqlString += "=" + val.ToString();
            }
            else
            {
                this.sqlString += "='" + val.ToString() + "'";
            }
            
            return this;
        }

        public SqlBuilder<T> And(object field)
        {
            this.sqlString += " and " + field.ToString();
            return this;
        }

        public SqlBuilder<T> Or(object field)
        {
            this.sqlString += " or " + field.ToString();
            return this;
        }

        public SqlBuilder<T> Not()
        {
            this.sqlString += " not ";
            return this;
        }

        public SqlBuilder<T> DESC()
        {
            this.sqlString += " DESC ";
            return this;
        }

        public SqlBuilder<T> ASC()
        {
            this.sqlString += " ASC ";
            return this;

        }

        public SqlBuilder<T> DESC(object field)
        {
            tmpc = this.sqlString.ToCharArray();
            int limit = this.sqlString.Length - 4;
            if ('A' == tmpc[limit]
                || 'E' == tmpc[limit])
            {
                this.sqlString += ",";
            }

            this.sqlString += field.ToString() + " DESC ";
            return this;
        }

        public SqlBuilder<T> ASC(object field)
        {
            tmpc = this.sqlString.ToCharArray();
            int limit = this.sqlString.Length - 4;
            if ('A' == tmpc[limit]
                || 'E' == tmpc[limit])
            {
                this.sqlString += ",";
            }

            this.sqlString += field.ToString() + " ASC ";
            return this;

        }

        public SqlBuilder<T> GroupBy(object field)
        {
            this.sqlString += " group by " + field.ToString();
            return this;
        }

        public SqlBuilder<T> GroupBy(object[] field)
        {
            tmp = " ";
            for (int iter = 0; iter < field.Length; iter++)
            {
                tmp += field[iter].ToString() + ",";
            }
            this.sqlString += tmp.Substring(0, tmp.Length - 1);
            return this;
        }

        public SqlBuilder<T> OrderBy(object field)
        {
            this.sqlString += " order by " 
                + field.GetType().ReflectedType.Name
                + "." 
                + field.ToString();
            return this;
        }

        public SqlBuilder<T> OrderBy()
        {
            this.sqlString += " order by ";
            return this;
        }

        public SqlBuilder<T> Having()
        {
            this.sqlString += " having ";
            return this;
        }


        public override string ToString()
        {
            return this.sqlString;
        }




    }//end SqlBuilder<T>
}
